<?php
class Controller_DataCenter_Warehouse extends Controller_Abstract
{
	function actionWarehousePeriod()
	{
		set_time_limit(0);
		$this->_view['title'] = '仓库周期表';
		$product_categories = Product_Categories::getCategoriesTree();
		$this->_view['product_categories'] = json_encode($product_categories);
		$this->_view['warehouse'] = Warehouse::getActiveWarehouse();
		$is_search = isset($_GET['is_search']) ? $_GET['is_search'] : '';
		if ($is_search)
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::StoragePeriod($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['result'] = $result['result'];
			}
		}
	}
	
	function actionOtherIOReport()
	{
		$this->_view['title'] = '其他出入库报表';
		if (! empty($_GET['is_search']))
		{
			$other_io = QDB::getConn()->execute('select other_io.id, other_io.storage_audit_time, other_io.number, other_io.type, other_io.notes, sum(other_io_item.quantity) as quantity, sum(other_io_item.cost_price*other_io_item.quantity) as amount, warehouse.name as warehouse_name from other_io left join other_io_item on other_io.id=other_io_item.other_io_id left join warehouse on warehouse.id=other_io.warehouse_id where other_io.storage_audit_time>=\''.$_GET['begin'].'\' and other_io.storage_audit_time<=\''.$_GET['end'].' 23:59:59\' group by other_io.id')->fetchAll();
			include Q::ini('custom_system/lib_dir').'PHPExcel.php';
			$objPHPExcel = new PHPExcel();
			$objPHPExcel->getProperties()->setCreator(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
			$objPHPExcel->getProperties()->setLastModifiedBy(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
			$objPHPExcel->getProperties()->setTitle("其他出入库");
			$objPHPExcel->getProperties()->setSubject("其他出入库");
			$objPHPExcel->getProperties()->setDescription("其他出入库-运营支撑系统");
			$objPHPExcel->getProperties()->setKeywords("其他出入库");
			$objPHPExcel->getProperties()->setCategory("其他出入库");
			$objPHPExcel->setActiveSheetIndex(0);
			$objPHPExcel->getActiveSheet()->setTitle("其他出入库");
			$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(16);
			$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(16);
			$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(18);
			$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
			$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
			$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(50);
			$objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFont()->setName('Candara');
			$objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFont()->setSize(15);
			$objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFont()->setBold(true);
			$objPHPExcel->getActiveSheet()->setCellValue('A1', "仓库审核日期");
			$objPHPExcel->getActiveSheet()->setCellValue('B1', "业务类型");
			$objPHPExcel->getActiveSheet()->setCellValue('C1', "仓库");
			$objPHPExcel->getActiveSheet()->setCellValue('D1', "单号");
			$objPHPExcel->getActiveSheet()->setCellValue('E1', "金额");
			$objPHPExcel->getActiveSheet()->setCellValue('F1', "数量");
			$objPHPExcel->getActiveSheet()->setCellValue('G1', "备注");
			$i = 2;
			foreach ($other_io as $io)
			{
				$objPHPExcel->getActiveSheet()->getStyle("A{$i}:F{$i}")->getFont()->setSize(12);
				$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $io['storage_audit_time']);
				$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, Helper_BSS_Normal::getIniOne('other_io_type', 'value', $io['type'], 'name'));
				$objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $io['warehouse_name']);
				$objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $io['number']);
				$objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $io['amount']);
				$objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $io['quantity']);
				$objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $io['notes']);
				$i++;
				$other_io_item = Other_Io_Item::find('other_io_item.other_io_id=?', $io['id'])
					->joinLeft('product_basic', 'product_basic.number as product_basic_number', 'product_basic.id=other_io_item.product_basic_id')
					->asArray()->getAll();
				$objPHPExcel->getActiveSheet()->getStyle("D{$i}:F{$i}")->getFont()->setName("Candara");
				$objPHPExcel->getActiveSheet()->getStyle("D{$i}:F{$i}")->getFont()->setSize(11);
				$objPHPExcel->getActiveSheet()->getStyle("D{$i}:F{$i}")->getFont()->setBold(true);
				$objPHPExcel->getActiveSheet()->setCellValue('D' . $i, '产品编号');
				$objPHPExcel->getActiveSheet()->setCellValue('E' . $i, '成本价');
				$objPHPExcel->getActiveSheet()->setCellValue('F' . $i, '数量');
				$i++;
				foreach ($other_io_item as $oii)
				{
					$objPHPExcel->getActiveSheet()->getCell('D' . $i)->setValueExplicit($oii['product_basic_number'], PHPExcel_Cell_DataType::TYPE_STRING);
					$objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $oii['cost_price']);
					$objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $oii['quantity']);
					$i++;
				}
			}
			$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
			$file_name = 'otherio-' . date('Y-m-d', CURRENT_TIMESTAMP) . '-' . CURRENT_USER_NAME . ".xls";
			$objWriter->save(Q::ini('custom_system/export_dir').$file_name);
			$this->_view['file_name'] = Q::ini('custom_system/export_url') . $file_name;
		}
	}
	
	function actionStockAnalysis()
	{
		$this->_view['title'] = '库存分析表';
		$purchase_categories = Product_Categories::getCategoriesTree();
		$this->_view['purchase_categories'] = json_encode($purchase_categories);
		$user = Purchase_Order::getUser();
		$this->_view['user'] = $user['data'];
		$warehouse = Warehouse::find()->asArray()->getAll();
		$this->_view['warehouse'] = $warehouse;
		$is_search = isset($_GET['is_search']) ? $_GET['is_search'] : '';
		if ($is_search)
		{
			$cp = isset($_GET['cp']) ? $_GET['cp'] : 0;
			$ps = isset($_GET['ps']) ? $_GET['ps'] : 20;
			$url_arr = Helper_BSS_Normal::buildCondition();
			$this->_view['url_arr_json'] = json_encode($url_arr);
			$result = Product_Warehouse::search($url_arr, $cp, $ps, 'product_warehouse.storage_number desc');
			if ($result['ack'] == SUCCESS)
			{
				$page = new Helper_BSS_Page($result['pagination'], url('DataCenter::Warehouse/StockAnalysis', $url_arr));
				$this->_view['page'] = $page->getPage();
				Helper_BSS_Normal::getProductImgUrl($result['data']);
				$this->_view['product'] = $result['data'];
				if (isset($url_arr['pass_date']) && $url_arr['pass_date'])
				{
					$this->_view['dates'] = explode(',', $url_arr['pass_date']);
				}
			}
		}
	}
	
	function actionSumByTOS()
	{
		$this->_view['title'] = '存货收发汇总表';
		$product_categories_data = Product_Categories::getCategoriesTree();
		$this->_view['product_categories_data'] = json_encode($product_categories_data);
		$warehouse = Warehouse::find()->asArray()->getAll();
		$this->_view['warehouse'] = $warehouse;
		if (! empty($_GET['is_search']))
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::SumByTOS($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['export_url'] = $result['export_url'];
			}
		}
	}
	
	function actionProductNotes()
	{
		$this->_view['title'] = '产品备注';
		$product = Product_basic::find('product_basic.id = ?', $_GET['id'])->asArray()->getOne();
		$this->_view['notes'] = $product['notes'];
	}
	
	function actionUpdateProductPurchaseStatusSingle()
	{
		$this->_view['title'] = '更新单个产品采购状态';
		$temp = explode('_', $_REQUEST['data']);
		$product_warehouse_id = $temp[0];
		$purchase_status = $temp[1];
		Product_Warehouse::updatePurchaseStatus($product_warehouse_id, 0, 0, $purchase_status, CURRENT_USER_ID);
		$ret = array(
				'ack' => 'success',
				'status_html' => "<font color='#".Helper_BSS_Normal::getIniOne('product_purchase_status', 'value', $purchase_status, 'color')."'>".Helper_BSS_Normal::getIniOne('product_purchase_status', 'value', $purchase_status, 'name')."</font>"
		);
		echo json_encode($ret);
		exit;
	}
	
	function actionUpdateProductSalesStatusSingle()
	{
		$this->_view['title'] = '更新单个产品销售状态';
		$temp = explode('_', $_REQUEST['data']);
		$product_warehouse_id = $temp[0];
		$sales_status = $temp[1];
		Product_Warehouse::updateSalesStatus($product_warehouse_id, 0, 0, $sales_status, CURRENT_USER_ID);
		$ret = array(
				'ack' => 'success',
				'status_html' => "<font color='#".Helper_BSS_Normal::getIniOne('product_sales_status', 'value', $sales_status, 'color')."'>".Helper_BSS_Normal::getIniOne('product_sales_status', 'value', $sales_status, 'name')."</font>"
		);
		echo json_encode($ret);
		exit;
	}
	
	function actionUpdateProductSalesStatusAllArtificial()
	{
		$this->_view['title'] = '更新产品状态';
		OLM_Cron::UpdateProductStatus();
	}
	
	function actionStockAnalysisDownload()
	{
		$this->_view['title'] = '请求下载存货分析表';
		set_time_limit(300);
		$url_arr = json_decode($_REQUEST['url_arr_json'], true);
		if (isset($url_arr['is_search']))
		{
			$result = Product_Warehouse::search($url_arr, 0, 0);
			$data = "产品编号\t产品名称\t销售状态\t采购状态\t可用\t不可用\t周销\t月销\t待入库数\t采购成本\t资料成本\t重量(g)\n";
			foreach ($result['data'] as $r)
			{
				$product_sales_status = Helper_BSS_Normal::getIniOne('product_sales_status', 'value', $r['sales_status'], 'name');
				$product_purchase_status= Helper_BSS_Normal::getIniOne('product_purchase_status', 'value', $r['purchase_status'], 'name');
				$unavailable = $r['actual_quantity']-$r['available_quantity'];
				$data .= "{$r['number']}\t{$r['product_basic_name']}\t{$product_sales_status}\t{$product_purchase_status}\t{$r['available_quantity']}\t{$unavailable}\t{$r['weekly_sales']}\t{$r['monthly_sales']}\t{$r['purchase_actual_quantity']}\t{$r['last_purchase_price']}\t{$r['purchase_price']}\t{$r['weight']}\n";
			}
			$data = iconv("UTF-8","UTF-16LE",$data);
			$data = "\xFF\xFE".$data;
			$file_name = 'inventory-' . date('YmdHis', CURRENT_TIMESTAMP) . '-' . CURRENT_USER_NAME . ".xls";
			$file = file_put_contents(Q::ini('custom_system/export_dir') . $file_name, $data);
			echo json_encode(array(
					'ack' => 'success',
					'url' =>Q::ini('custom_system/export_url') . $file_name
			));
			exit;
		}
		else
		{
			echo json_encode(array('ack' => 'failure'));
		}
	}
	
	function actionExportResendRate()
	{
		$this->_view['title'] = '导出重发率报表';
		$default_warehouse_id = Warehouse::getDefaultWarehouseId();
		$url_arr = Helper_BSS_Normal::buildCondition();
		$where = 'sales_order.resend_reason in (' . Q::ini('custom_flag/sales_order_resend_reason/wrong_delivery/value') . ',' . Q::ini('custom_flag/sales_order_resend_reason/missed_delivery/value') . ') and sales_order.handle_status=' . Q::ini('custom_flag/sales_order_handle_status/shipped/value') . ' and sales_order.send_method=' . Q::ini('custom_flag/sales_order_send_method/resend/value');
		if (! empty($url_arr['begin_shipped_time']))
		{
			$where .= ' and sales_order.shipped_time>\'' . $url_arr['begin_shipped_time'] . '\'';
		}
		if (! empty($url_arr['end_shipped_time']))
		{
			$where .= ' and sales_order.shipped_time<=\'' . $url_arr['end_shipped_time'] . ' 23:59:59\'';
		}
		if (! empty($url_arr['shop_id']))
		{
			$where .= ' and sales_order.shop_id=' . $url_arr['shop_id'];
		}
		$product = Sales_Order_Item::find($where)
			->joinLeft('sales_order', '', 'sales_order.id=sales_order_item.sales_order_id')
			->joinLeft('product_basic', '', 'product_basic.id=sales_order_item.product_basic_id')
			->joinLeft('product_warehouse', '', 'product_basic.id=product_warehouse.product_basic_id and product_warehouse.warehouse_id='.$default_warehouse_id)
			->distinct('product_basic.id')
			->setColumns('product_basic.number, product_warehouse.shelf_position, product_warehouse.actual_quantity')->asArray()->getAll();
		include Q::ini('custom_system/lib_dir').'PHPExcel.php';
		$objPHPExcel = new PHPExcel();
		$objPHPExcel->getProperties()->setCreator(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
		$objPHPExcel->getProperties()->setLastModifiedBy(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
		$objPHPExcel->getProperties()->setTitle('错漏发货产品');
		$objPHPExcel->getProperties()->setSubject('错漏发货产品');
		$objPHPExcel->getProperties()->setDescription('错漏发货产品-运营支撑系统');
		$objPHPExcel->getProperties()->setKeywords('错漏发货产品');
		$objPHPExcel->getProperties()->setCategory('错漏发货产品');
		$objPHPExcel->setActiveSheetIndex(0);
		$objPHPExcel->getActiveSheet()->setTitle('错漏发货产品');
		$objPHPExcel->getActiveSheet()->setCellValue('A1', '产品编号');
		$objPHPExcel->getActiveSheet()->setCellValue('B1', '架位');
		$objPHPExcel->getActiveSheet()->setCellValue('C1', '实际库存');
		$i = 2;
		foreach ($product as $p)
		{
			$objPHPExcel->getActiveSheet()->getCell('A'.$i)->setValueExplicit($p['number'], PHPExcel_Cell_DataType::TYPE_STRING);
			$objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $p['shelf_position']);
			$objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $p['actual_quantity']);
			$i++;
		}
		$outputFileName = "ExportResendRate.xls";
		$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
		header("Content-Type: application/vnd.ms-execl");
		header("Content-Type: application/octet-stream");
		header("Content-Type: application/download");
		header('Content-Disposition:inline;filename="'.$outputFileName.'"');
		header("Content-Transfer-Encoding: binary");
		header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
		header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
		header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
		header("Pragma: no-cache");
		$objWriter->save('php://output');
		exit;
	}
	
	function actionResendRate()
	{
		$this->_view['title'] = '重发率报表';
		$shop = Shop::getActiveShop();
		$this->_view['shop'] = $shop;
		if ( ! empty($_GET['is_search']))
		{
			set_time_limit(0);
			$url_arr = Helper_BSS_Normal::buildCondition();
			$where = '1';
			if (! empty($url_arr['begin_shipped_time']))
			{
				$where .= ' and sales_order.shipped_time>\'' . $url_arr['begin_shipped_time'] . '\'';
			}
			if (! empty($url_arr['end_shipped_time']))
			{
				$where .= ' and sales_order.shipped_time<=\'' . $url_arr['end_shipped_time'] . ' 23:59:59\'';
			}
			if (! empty($url_arr['shop_id']))
			{
				$where .= ' and sales_order.shop_id=' . $url_arr['shop_id'];
			}
			$where .= ' and sales_order.handle_status=' . Q::ini('custom_flag/sales_order_handle_status/shipped/value');
			$all_count = Sales_Order::find($where)->getCount();
			$where .= ' and sales_order.send_method=' . Q::ini('custom_flag/sales_order_send_method/resend/value');
			$resend_count = Sales_Order::find($where)->getCount();
			$reason = array();
			foreach (Q::ini('custom_flag/sales_order_resend_reason') as $k => $r)
			{
				$reason[$k]['name'] = $r['name'];
				$sales_order = Sales_Order::find($where . ' and sales_order.resend_reason=' . $r['value'])
					->joinLeft('sales_order_customer_info', 'sales_order_customer_info.country as country', 'sales_order_customer_info.sales_order_id=sales_order.id')
					->asArray()->getAll();
				$reason[$k]['count'] = count($sales_order);
				$product = array();
				$product_all_count = 0;
				$shipping_method = array();
				$country = array();
				foreach ($sales_order as $so)
				{
					$resend_order = Sales_Order::find('sales_order.id=?', $so['resend_sales_order_id'])
						->joinLeft('shipping_method', 'name as shipping_method_name', 'shipping_method.id=sales_order.shipping_method')
						->asArray()->getOne();
					if ($resend_order)
					{
						if ( ! array_key_exists($resend_order['shipping_method_name'], $shipping_method))
						{
							$shipping_method[$resend_order['shipping_method_name']] = 0;
						}
						$shipping_method[$resend_order['shipping_method_name']]++;
					}
					if ( ! array_key_exists($so['country'], $country))
					{
						$country[$so['country']] = 0;
					}
					$country[$so['country']]++;
					$item = Sales_Order_Item::find('sales_order_id=?', $so['id'])->asArray()->getAll();
					foreach ($item as $i)
					{
						if ( ! array_key_exists($i['product_basic_id'], $product))
						{
							$p = Product_basic::find('id=?', $i['product_basic_id'])->asArray()->getAll();
							Helper_BSS_Normal::getProductImgUrl($p);
							$product[$i['product_basic_id']]['name'] = $p[0]['name'];
							$product[$i['product_basic_id']]['number'] = $p[0]['number'];
							$product[$i['product_basic_id']]['pic_url'] = $p[0]['pic_url'];
							$product[$i['product_basic_id']]['count'] = 0;
						}
						$product[$i['product_basic_id']]['count'] += $i['quantity'];
						$product_all_count += $i['quantity'];
					}
				}
				$reason[$k]['product']['count'] = $product_all_count;
				$reason[$k]['product']['detail'] = $product;
				$reason[$k]['shipping_method'] = $shipping_method;
				$reason[$k]['country'] = $country;
			}
			$this->_view['all_count'] = $all_count;
			$this->_view['resend_count'] = $resend_count;
			$this->_view['reason'] = $reason;
		}
	}
	
	function actionClearanceRecordLog()
	{
		$this->_view['title'] = '清仓记录表';
		if (isset($_GET['is_search']))
		{
			$where = '1';
			if (! empty($_GET['begin']))
			{
				$where .= ' and clearance_record_log.update_time>=\'' . $_GET['begin'] . '\'';  
			}
			if (! empty($_GET['end']))
			{
				$where .= ' and clearance_record_log.update_time<=\'' . $_GET['end'] . '\'';
			}
			if (! empty($_GET['number']))
			{
				$where .= ' and product_basic.number=\'' . $_GET['number'] . '\'';
			}
			$result = QDB::getConn()->execute('select product_basic.name, product_basic.number, clearance_record_log.* from clearance_record_log left join product_basic on product_basic.id=clearance_record_log.product_basic_id where ' . $where)->fetchAll();
			Helper_BSS_Normal::getProductImgUrl($result);
			$this->_view['data'] = $result;
		}
	}

	function actionInventoryChart()
	{
		set_time_limit(0);
		$this->_view['title'] = '库存统计报表';
		$this->_view['warehouse'] = Warehouse::find()->asArray()->getAll();
		$product_categories = Product_Categories::getCategoriesTree();
		$this->_view['product_categories'] = json_encode($product_categories);
		if (isset($_GET['is_search']) && $_GET['is_search'] == 1)
		{
			$condition = Helper_BSS_Normal::buildCondition();
			if (strtotime($condition['begin_report_time']) < strtotime($condition['end_report_time']))
			{
				$result = OLM_Report::inventoryReport($condition);
				if ($result['ack'] == SUCCESS)
				{
					$this->_view['show_chart'] = 1;
					$this->_view['report'] = $result['report'];
					$this->_view['data_json'] = json_encode($result['chart_data'], JSON_NUMERIC_CHECK);
					if (isset($_GET['warehouse']))
					{
						$report_warehouse = Warehouse::find('id in(' . implode(',', $_GET['warehouse']) . ')')->asArray()->getAll();
						$warehouse_name = array();
						foreach ($report_warehouse as $rw)
						{
							$warehouse_name[] = $rw['name'];
						}
						$this->_view['warehouse_name'] = implode(' | ', $warehouse_name);
					}
				}
			}
		}
	}
	
	function actionOperationReport()
	{
		$this->_view['title'] = '仓库操作统计报表';
		$parent = User::find('id=?', Q::ini('custom_flag/info/warehouse_manager/value'))->asArray()->getAll();
		$child = User::getOffspringById(Q::ini('custom_flag/info/warehouse_manager/value'));
		$this->_view['user'] = array_merge($parent, $child);
		if (! empty($_GET['is_search']))
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			if ( ! empty($url_arr['begin_shipped_time']) && ! empty($url_arr['end_shipped_time']) && ! empty($url_arr['user_id']))
			{
				if (strtotime($url_arr['begin_shipped_time']) <= strtotime($url_arr['end_shipped_time']))
				{
					$result = OLM_Report::operationreport($url_arr);
					if ($result['ack'] == SUCCESS)
					{
						$this->_view['report'] = $result['data'];
					}
					else
					{
						return $this->_redirectMessage('查询失败！', $result['message'], $_SERVER['HTTP_REFERER'], $result['ack']);
					}
				}
			}
		}
	}
}